CREATE DATABASE PGR_TSQL
GO

USE PGR_TSQL
GO

SELECT * INTO TABLA_PRUEBA FROM DETALLE_SOCIOS
GO
DELETE FROM TABLA_PRUEBA
GO

DECLARE @CONTADOR INT
SET @CONTADOR = 1
SET DATEFORMAT YMD
DECLARE @D1 DATETIME = '1950-01-01'
DECLARE @D2 DATETIME = '1994-12-31'
DECLARE @D3 DATETIME
DECLARE @D4 DATETIME
DECLARE @CONT VARCHAR(4)
DECLARE @DUI VARCHAR(9)
WHILE (@CONTADOR <= 100000)
BEGIN
	SET @CONT = CAST(@CONTADOR AS VARCHAR)
	SET @D3 = DATEADD(DAY, (ABS(CHECKSUM(NEWID())) % (1 + DATEDIFF(DAY, @D1, @D2))), @D1)
	SET @D4 = DATEADD(DAY, (ABS(CHECKSUM(NEWID())) % (1 + DATEDIFF(DAY, @D2, GETDATE()))), @D1)
	SET @DUI = STUFF(@CONT, 1, 0, REPLICATE('0', 9 - LEN(@CONT)))
	INSERT INTO TABLA_PRUEBA (ID_SOCIO, PRIMER_NOMBRE, PRIMER_APELLIDO, FECHA_NACIMIENTO, DIRECCION, CIUDAD, ESTADO, CORREO, FECHA_INGRESO, DUI) VALUES (
		@CONTADOR,
		'NOMBRE ' + @CONT,
		'APELLIDO ' + @CONT,
		@D3,
		'DIRECCION ' + @CONT,
		'CIUDAD ' + @CONT,
		'ESTADO ' + @CONT,
		'correo' + @CONT + '@mail.com',
		@D4,
		@DUI
	)
	SET @CONTADOR = @CONTADOR + 1
END

/*
TRUNCATE TABLE TABLA_PRUEBA
GO
*/

/*
SELECT * FROM TABLA_PRUEBA ORDER BY ID_SOCIO
GO
*/

SET DATEFORMAT YMD
GO

UPDATE TABLA_PRUEBA SET TELEFONO = NULL
GO

DECLARE @START DATETIME = GETDATE()
DECLARE @ID INT, @TELEFONO VARCHAR(10)
DECLARE PGR_CURSOR CURSOR /*FAST_FORWARD*/ /*READ_ONLY*/ FOR SELECT ID_SOCIO FROM TABLA_PRUEBA
OPEN PGR_CURSOR
	FETCH PGR_CURSOR INTO @ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @TELEFONO = '999999' + RIGHT(DUI, 4) FROM TABLA_PRUEBA WHERE ID_SOCIO = @ID
		UPDATE TABLA_PRUEBA SET TELEFONO = @TELEFONO WHERE ID_SOCIO = @ID
		FETCH NEXT FROM PGR_CURSOR INTO @ID
	END
CLOSE PGR_CURSOR
DEALLOCATE PGR_CURSOR
SELECT DATEDIFF(SS, @START, GETDATE()) AS TIEMPO_TRANSCURRIDO_SEG

UPDATE TABLA_PRUEBA SET TELEFONO = NULL
GO

DECLARE @START DATETIME = GETDATE()
DECLARE @TEMP_TABLE TABLE (ID_SOCIO INT, DUI VARCHAR(9))
INSERT INTO @TEMP_TABLE SELECT ID_SOCIO, DUI FROM TABLA_PRUEBA
UPDATE TABLA_PRUEBA
SET TELEFONO = (SELECT '999999' + RIGHT(T1.DUI, 4) FROM @TEMP_TABLE T1 WHERE TABLA_PRUEBA.ID_SOCIO = T1.ID_SOCIO)
WHERE TABLA_PRUEBA.ID_SOCIO IN (SELECT T2.ID_SOCIO FROM @TEMP_TABLE T2)
SELECT DATEDIFF(SS, @START, GETDATE()) AS TIEMPO_TRANSCURRIDO_SEG

